
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 02/29/2012 10:18:20
-- Generated from EDMX file: C:\School\MvcImageSample\Models\ImageModel.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [image];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints w/o capitals
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_image_categories1]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[image_categories] DROP CONSTRAINT [FK_image_categories1];
GO
IF OBJECT_ID(N'[dbo].[FK_image_categories2]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[image_categories] DROP CONSTRAINT [FK_image_categories2];
GO
IF OBJECT_ID(N'[dbo].[FK_image_user_tags1]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[image_user_tags] DROP CONSTRAINT [FK_image_user_tags1];
GO
IF OBJECT_ID(N'[dbo].[FK_image_user_tags2]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[image_user_tags] DROP CONSTRAINT [FK_image_user_tags2];
GO
IF OBJECT_ID(N'[dbo].[FK_user_image_ratings1]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[user_image_ratings] DROP CONSTRAINT [FK_user_image_ratings1];
GO
IF OBJECT_ID(N'[dbo].[FK_user_image_ratings2]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[user_image_ratings] DROP CONSTRAINT [FK_user_image_ratings2];
GO
IF OBJECT_ID(N'[dbo].[FK_user_images1]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[user_images] DROP CONSTRAINT [FK_user_images1];
GO
IF OBJECT_ID(N'[dbo].[FK_user_images2]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[user_images] DROP CONSTRAINT [FK_user_images2];
GO

-- --------------------------------------------------
-- Dropping plural FOREIGN KEY constraints, w capitals
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_image_categories1]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[image_categories] DROP CONSTRAINT [FK_image_categories1];
GO
IF OBJECT_ID(N'[dbo].[FK_image_categories2]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[image_categories] DROP CONSTRAINT [FK_image_categories2];
GO
IF OBJECT_ID(N'[dbo].[FK_image_user_tags1]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[image_user_tags] DROP CONSTRAINT [FK_image_user_tags1];
GO
IF OBJECT_ID(N'[dbo].[FK_image_user_tags2]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[image_user_tags] DROP CONSTRAINT [FK_image_user_tags2];
GO
IF OBJECT_ID(N'[dbo].[FK_user_image_ratings1]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[user_image_ratings] DROP CONSTRAINT [FK_user_image_ratings1];
GO
IF OBJECT_ID(N'[dbo].[FK_user_image_ratings2]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[user_image_ratings] DROP CONSTRAINT [FK_user_image_ratings2];
GO
IF OBJECT_ID(N'[dbo].[FK_user_images1]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[user_images] DROP CONSTRAINT [FK_user_images1];
GO
IF OBJECT_ID(N'[dbo].[FK_user_images2]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[user_images] DROP CONSTRAINT [FK_user_images2];
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints, w capitals
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[FK_ImageCategory1]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ImageCategory] DROP CONSTRAINT [FK_ImageCategory1];
GO
IF OBJECT_ID(N'[dbo].[FK_ImageCategory2]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ImageCategory] DROP CONSTRAINT [FK_ImageCategory2];
GO
IF OBJECT_ID(N'[dbo].[FK_ImageUserTag1]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ImageUserTag] DROP CONSTRAINT [FK_ImageUserTag1];
GO
IF OBJECT_ID(N'[dbo].[FK_ImageUserTag2]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[ImageUserTag] DROP CONSTRAINT [FK_ImageUserTag2];
GO
IF OBJECT_ID(N'[dbo].[FK_UserImageRating1]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserImageRating] DROP CONSTRAINT [FK_UserImageRating1];
GO
IF OBJECT_ID(N'[dbo].[FK_UserImageRating2]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserImageRating] DROP CONSTRAINT [FK_UserImageRating2];
GO
IF OBJECT_ID(N'[dbo].[FK_UserImage1]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserImage] DROP CONSTRAINT [FK_UserImage1];
GO
IF OBJECT_ID(N'[dbo].[FK_UserImage2]', 'F') IS NOT NULL
    ALTER TABLE [dbo].[UserImage] DROP CONSTRAINT [FK_UserImage2];
GO

-- --------------------------------------------------
-- Dropping original tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[categories]', 'U') IS NOT NULL
    DROP TABLE [dbo].[categories];
GO
IF OBJECT_ID(N'[dbo].[image_categories]', 'U') IS NOT NULL
    DROP TABLE [dbo].[image_categories];
GO
IF OBJECT_ID(N'[dbo].[image_user_tags]', 'U') IS NOT NULL
    DROP TABLE [dbo].[image_user_tags];
GO
IF OBJECT_ID(N'[dbo].[images]', 'U') IS NOT NULL
    DROP TABLE [dbo].[images];
GO
IF OBJECT_ID(N'[dbo].[user_image_ratings]', 'U') IS NOT NULL
    DROP TABLE [dbo].[user_image_ratings];
GO
IF OBJECT_ID(N'[dbo].[user_images]', 'U') IS NOT NULL
    DROP TABLE [dbo].[user_images];
GO
IF OBJECT_ID(N'[dbo].[users]', 'U') IS NOT NULL
    DROP TABLE [dbo].[users];
GO

-- --------------------------------------------------
-- Dropping old plural tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[categories]', 'U') IS NOT NULL
    DROP TABLE [dbo].[categories];
GO
IF OBJECT_ID(N'[dbo].[image_categories]', 'U') IS NOT NULL
    DROP TABLE [dbo].[image_categories];
GO
IF OBJECT_ID(N'[dbo].[image_user_tags]', 'U') IS NOT NULL
    DROP TABLE [dbo].[image_user_tags];
GO
IF OBJECT_ID(N'[dbo].[images]', 'U') IS NOT NULL
    DROP TABLE [dbo].[images];
GO
IF OBJECT_ID(N'[dbo].[user_image_ratings]', 'U') IS NOT NULL
    DROP TABLE [dbo].[user_image_ratings];
GO
IF OBJECT_ID(N'[dbo].[user_images]', 'U') IS NOT NULL
    DROP TABLE [dbo].[user_images];
GO
IF OBJECT_ID(N'[dbo].[users]', 'U') IS NOT NULL
    DROP TABLE [dbo].[users];
GO


-- --------------------------------------------------
-- Dropping existing singular tables
-- --------------------------------------------------

IF OBJECT_ID(N'[dbo].[Category]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Category];
GO
IF OBJECT_ID(N'[dbo].[ImageCategory]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ImageCategory];
GO
IF OBJECT_ID(N'[dbo].[ImageUserTag]', 'U') IS NOT NULL
    DROP TABLE [dbo].[ImageUserTag];
GO
IF OBJECT_ID(N'[dbo].[Image]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Image];
GO
IF OBJECT_ID(N'[dbo].[UserImageRating]', 'U') IS NOT NULL
    DROP TABLE [dbo].[UserImageRating];
GO
IF OBJECT_ID(N'[dbo].[UserImage]', 'U') IS NOT NULL
    DROP TABLE [dbo].[UserImage];
GO
IF OBJECT_ID(N'[dbo].[RegisteredUser]', 'U') IS NOT NULL
    DROP TABLE [dbo].[RegisteredUser];
GO
IF OBJECT_ID(N'[dbo].[Rating]', 'U') IS NOT NULL
    DROP TABLE [dbo].[Rating];
GO
-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'Category'
CREATE TABLE [dbo].[Category] (
    [categoryid] int  NOT NULL,
    [category_name] varchar(max)  NOT NULL
);
GO

-- Creating table 'ImageCategory'
CREATE TABLE [dbo].[ImageCategory] (
    [image_categoryid] int  NOT NULL,
    [imageid] int  NOT NULL,
    [categoryid] int  NOT NULL
);
GO

-- Creating table 'ImageUserTag'
CREATE TABLE [dbo].[ImageUserTag] (
    [image_user_tagid] int  NOT NULL,
    [userid] int  NOT NULL,
    [imageid] int  NOT NULL,
    [tag] varchar(max)  NOT NULL,
    [tag_date] datetime  NULL
);
GO

-- Creating table 'Image'
CREATE TABLE [dbo].[Image] (
    [imageid] int  NOT NULL,
    [source_url] varchar(max)  NULL,
    [typeid] int  NOT NULL,
    [user_upload_comment] varchar(max)  NULL,
    [location] varchar(max)  NULL,
    [name] varchar(max) NOT NULL,
    [image_file] image NULL,
    [upload_date] datetime  NULL
);
GO

-- Creating table 'UserImageRating'
CREATE TABLE [dbo].[UserImageRating] (
    [user_image_rating_id] int  NOT NULL,
    [userid] int  NOT NULL,
    [imageid] int  NOT NULL,
    [rating] int  NOT NULL,
    [rating_date] datetime  NULL
);
GO

-- Creating table 'UserImage'
CREATE TABLE [dbo].[UserImage] (
    [user_image_id] int  NOT NULL,
    [userid] int  NOT NULL,
    [imageid] int  NOT NULL,
    [favorite] char(1)  NULL
);
GO

-- Creating table 'RegisteredUser'
CREATE TABLE [dbo].[RegisteredUser] (
    [userid] int  NOT NULL,
    [email] varchar(max)  NOT NULL,
    [password] varchar(max)  NOT NULL,
    [alias] varchar(max)  NOT NULL,
    [firstname] varchar(max)  NULL,
    [lastname] varchar(max)  NULL,
	[create_date] datetime NULL
);
GO

-- Creating table 'Rating' to hold rating params
CREATE TABLE [dbo].[Rating] (
    [ratingid] int  NOT NULL,
	[description] varchar(80) NULL
);
GO

-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [categoryid] in table 'Category'
ALTER TABLE [dbo].[Category]
ADD CONSTRAINT [PK_Category]
    PRIMARY KEY CLUSTERED ([categoryid] ASC);
GO

-- Creating primary key on [image_categoryid] in table 'ImageCategory'
ALTER TABLE [dbo].[ImageCategory]
ADD CONSTRAINT [PK_ImageCategory]
    PRIMARY KEY CLUSTERED ([image_categoryid] ASC);
GO

-- Creating primary key on [image_user_tagid] in table 'ImageUserTag'
ALTER TABLE [dbo].[ImageUserTag]
ADD CONSTRAINT [PK_ImageUserTag]
    PRIMARY KEY CLUSTERED ([image_user_tagid] ASC);
GO

-- Creating primary key on [imageid] in table 'Image'
ALTER TABLE [dbo].[Image]
ADD CONSTRAINT [PK_Image]
    PRIMARY KEY CLUSTERED ([imageid] ASC);
GO

-- Creating primary key on [user_image_rating_id] in table 'UserImageRating'
ALTER TABLE [dbo].[UserImageRating]
ADD CONSTRAINT [PK_UserImageRating]
    PRIMARY KEY CLUSTERED ([user_image_rating_id] ASC);
GO

-- Creating primary key on [user_image_id] in table 'UserImage'
ALTER TABLE [dbo].[UserImage]
ADD CONSTRAINT [PK_UserImage]
    PRIMARY KEY CLUSTERED ([user_image_id] ASC);
GO

-- Creating primary key on [userid] in table 'RegisteredUser'
ALTER TABLE [dbo].[RegisteredUser]
ADD CONSTRAINT [PK_RegisteredUser]
    PRIMARY KEY CLUSTERED ([userid] ASC);
GO

-- Creating primary key on [Rating] in table 'ratingid'
ALTER TABLE [dbo].[Rating]
ADD CONSTRAINT [PK_Rating]
    PRIMARY KEY CLUSTERED ([ratingid] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------
USE image
GO

/**ALTER TABLE [dbo].[ImageCategory] DROP CONSTRAINT FK_ImageCategory2 **/
-- Creating foreign key on [categoryid] in table 'ImageCategory'
ALTER TABLE [dbo].[ImageCategory]
ADD CONSTRAINT [FK_ImageCategory2]
    FOREIGN KEY ([categoryid])
    REFERENCES [dbo].[Category]
        ([categoryid])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ImageCategory2'
CREATE INDEX [IX_FK_ImageCategory2]
ON [dbo].[ImageCategory]
    ([categoryid]);
GO

-- Creating foreign key on [imageid] in table 'ImageCategory'
ALTER TABLE [dbo].[ImageCategory]
ADD CONSTRAINT [FK_ImageCategory1]
    FOREIGN KEY ([imageid])
    REFERENCES [dbo].[Image]
        ([imageid])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ImageCategory1'
CREATE INDEX [IX_FK_ImageCategory1]
ON [dbo].[ImageCategory]
    ([imageid]);
GO

-- Creating foreign key on [userid] in table 'ImageUserTag'
ALTER TABLE [dbo].[ImageUserTag]
ADD CONSTRAINT [FK_ImageUserTag1]
    FOREIGN KEY ([userid])
    REFERENCES [dbo].[RegisteredUser]
        ([userid])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_ImageUserTag1'
CREATE INDEX [IX_FK_ImageUserTag1]
ON [dbo].[ImageUserTag]
    ([userid]);
GO

-- Creating foreign key on [image_user_tagid] in table 'ImageUserTag'
ALTER TABLE [dbo].[ImageUserTag]
ADD CONSTRAINT [FK_ImageUserTag2]
    FOREIGN KEY ([image_user_tagid])
    REFERENCES [dbo].[Image]
        ([imageid])
    ON DELETE NO ACTION ON UPDATE NO ACTION;
GO

-- Creating foreign key on [imageid] in table 'UserImageRating'
ALTER TABLE [dbo].[UserImageRating]
ADD CONSTRAINT [FK_UserImageRating2]
    FOREIGN KEY ([imageid])
    REFERENCES [dbo].[Image]
        ([imageid])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserImageRating2'
CREATE INDEX [IX_FK_UserImageRating2]
ON [dbo].[UserImageRating]
    ([imageid]);
GO

-- Creating foreign key on [imageid] in table 'UserImage'
ALTER TABLE [dbo].[UserImage]
ADD CONSTRAINT [FK_UserImage2]
    FOREIGN KEY ([imageid])
    REFERENCES [dbo].[Image]
        ([imageid])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserImage2'
CREATE INDEX [IX_FK_UserImage2]
ON [dbo].[UserImage]
    ([imageid]);
GO

-- Creating foreign key on [userid] in table 'UserImageRating'
ALTER TABLE [dbo].[UserImageRating]
ADD CONSTRAINT [FK_UserImageRating1]
    FOREIGN KEY ([userid])
    REFERENCES [dbo].[RegisteredUser]
        ([userid])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserImageRating1'
CREATE INDEX [IX_FK_UserImageRating1]
ON [dbo].[UserImageRating]
    ([userid]);
GO

-- Creating foreign key on [userid] in table 'UserImage'
ALTER TABLE [dbo].[UserImage]
ADD CONSTRAINT [FK_UserImage1]
    FOREIGN KEY ([userid])
    REFERENCES [dbo].[RegisteredUser]
        ([userid])
    ON DELETE CASCADE ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_UserImage1'
CREATE INDEX [IX_FK_UserImage1]
ON [dbo].[UserImage]
    ([userid]);
GO


-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------